package com.cn.wanxi.dao.impl;

import com.cn.wanxi.dao.UserDao;
import com.cn.wanxi.model.PageModel;
import com.cn.wanxi.model.ResultModel;
import com.cn.wanxi.model.UserModel;
import com.cn.wanxi.util.JDBC;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class UserImpl implements UserDao {
    /**
     * 需要查询数据库
     * 这里面做什么事情呢？
     * 1.连接数据库--怎么连接数据库呢？手写JDBC
     * 2.根据数据库的返回值进行逻辑处理
     *
     * @param userModel
     * @return
     */
    @Override
    public UserModel findByUsernameAndPassword(UserModel userModel) {
        String sql = "SELECT * FROM user where username='" + userModel.getUsername() + "' and password='" + userModel.getPassword() + "'";
        ResultSet resultSet = JDBC.query(sql);
        UserModel model = null;
        try {
//            如果查询出有值，则进入while循环
            while (resultSet.next()) {
                model = new UserModel();
            }
        } catch (Exception e) {

        }
        return model;
    }

    @Override
    public List<UserModel> findAll(UserModel model) {
        String sql = "select *,date_format(birthday,'%Y-%m-%d') temp from user order by id desc ";
//        Integer pageNo=(model.getPageNo()-1)*model.getPageSize();
        sql += " limit " + (model.getPageNo() + -1) * +model.getPageSize() + "," + model.getPageSize();
        return getData(sql);
    }

    @Override
    public int getCount(UserModel model) {
        String sql = "select count(*) count from user";
        ResultSet resultSet = JDBC.query(sql);
        int count = 0;
        try {
            while (resultSet.next()) {
                count = resultSet.getInt("count");
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return count;
    }

    @Override
    public int delete(UserModel model) {
        String sql = "delete from user where id=" + model.getId();

        return JDBC.update(sql);
    }

    @Override
    public int add(UserModel model) {
        String sql = "INSERT INTO `user` " +
                "(`username`, `address`, `create_time`, " +
                "`email`, `enable`, `password`," +
                " `phone`, `sex`, `update_time`, " +
                "`hobby`, `birthday`, `remark`) VALUES " +
                "('" + model.getUsername() + "', '', now()," +
                " '" + model.getEmail() + "', '" + model.getEnable() + "', 'password', " +
                "'" + model.getPhone() + "', '" + model.getSex() + "', now()," +
                " '" + model.getHobby() + "', '" + model.getBirthday() + "', '');\n";

        return JDBC.update(sql);
    }

    @Override
    public int updateSex(UserModel model) {
//        update user set sex= case sex when '男' then '女' else '男' end where id=235;
//        update user set sex=if(sex='男','女','男') where id=235;
        String sql = "update user set sex=if(sex='男','女','男') where id=" + model.getId();
        return JDBC.update(sql);
    }

    @Override
    public int updateEnable(UserModel model) {
//        update user set enable=enable^1 where id=235;
//        update user set enable=abs(enable-1) where id=235;
        String sql = "update user set enable=abs(enable-1) where id=" + model.getId();
        return JDBC.update(sql);
    }

    @Override
    public UserModel findById(UserModel model) {
        String sql = "select *,date_format(birthday,'%Y-%m-%d') temp from user where id=" + model.getId();

        List<UserModel> list = getData(sql);
        return list.size() > 0 ? list.get(0) : null;
    }

    @Override
    public int update(UserModel userModel) {
        String sql = "UPDATE `user` SET " +
                "`username` = '" + userModel.getUsername() + "',";
        if (userModel.getAddress() != null) {
            sql += " `address` = '" + userModel.getAddress() + "',";
        }


        sql += " `email` = '" + userModel.getEmail() + "'," +
                " `enable` = '" + userModel.getEnable() + "'," +
                " `phone` = '" + userModel.getPhone() + "', " +
                "`sex` = '" + userModel.getSex() + "', " +
                "`update_time` =now()," +
                " `hobby` = '" + userModel.getHobby() + "', ";
        if (userModel.getBirthday() != null) {
            sql += "`birthday` = '" + userModel.getBirthday() + "',";
        }

        sql += " `remark` = '' ";
        sql += "WHERE (`id` = '"+userModel.getId()+"');\n";
        return JDBC.update(sql);
    }

    private List<UserModel> getData(String sql) {
        ResultSet resultSet = JDBC.query(sql);
        List<UserModel> list = new ArrayList<>();
        try {
            while (resultSet.next()) {
                UserModel userModel = new UserModel();
                userModel.setUsername(resultSet.getString("username"));
                userModel.setBirthday(resultSet.getString("temp"));
                userModel.setPhone(resultSet.getString("phone"));
                userModel.setId(resultSet.getInt("id"));
                userModel.setEnable(resultSet.getInt("enable"));
                userModel.setSex(resultSet.getString("sex"));
                userModel.setHobby(resultSet.getString("hobby"));
                userModel.setEmail(resultSet.getString("email"));
                list.add(userModel);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

}
